﻿using BingSpatialDataServiceExcelAddin.BSDSTools.Models;
using System;
using System.ComponentModel;
using System.IO;
using System.Net;
using System.Runtime.Serialization.Json;
using System.Text;
using System.Threading;
using Excel = Microsoft.Office.Interop.Excel;

namespace BingSpatialDataServiceExcelAddin.BSDSTools
{
    public class DataSourceService
    {
        #region Public Actions 

        public Action<Response> UploadCompleted;

        public Action<string> DownloadCompleted;

        public Action<string> ProgressChanged;

        #endregion

        #region Public Methods

        public void Upload(Settings settings)
        {
            BackgroundWorker bw = new BackgroundWorker();
            bw.WorkerReportsProgress = true;

            bw.RunWorkerCompleted += (sender, args) =>
            {
                if (UploadCompleted != null)
                {
                    UploadCompleted(args.Result as Response);
                }
            };

            bw.ProgressChanged += (sender, args) =>
            {
                if (ProgressChanged != null)
                {
                    ProgressChanged(args.UserState as string);
                }
            };

            bw.DoWork += (sender, args) =>
            {
                try
                {
                    bw.ReportProgress(0, "Creating Upload Job.");

                    string dataflowJobLocation = CreateUploadJob(settings);

                    //Continue to check the dataflow job status until the job has completed
                    Response statusDetails;
                    DataflowJob job = null;
                    do
                    {
                        statusDetails = CheckStatus(dataflowJobLocation, settings.MasterKey);

                        if (statusDetails != null
                            && statusDetails.ResourceSets != null
                            && statusDetails.ResourceSets.Length > 0
                            && statusDetails.ResourceSets[0] != null
                            && statusDetails.ResourceSets[0].Resources != null
                            && statusDetails.ResourceSets[0].Resources.Length > 0
                            && statusDetails.ResourceSets[0].Resources[0] != null)
                        {
                            job = statusDetails.ResourceSets[0].Resources[0];
                            if (job.Status == "Aborted")
                            {
                                bw.ReportProgress(0, "Upload Job Aborted.");
                                throw new Exception("Job was aborted due to an error.");
                            }
                        }

                        if (job == null || job.Status.Equals("Pending"))
                        {
                            Thread.Sleep(15000); //Get status every 15 seconds
                        }
                    }
                    while (job != null && job.Status != null && job.Status.Equals("Pending"));

                    args.Result = statusDetails;
                }
                catch (Exception e)
                {
                    args.Result = new Response()
                    {
                        ErrorDetails = new string[] { e.Message }
                    };
                }
            };
            bw.RunWorkerAsync();
        }

        public void Download(string fileName, string accessId, string dataSourceName, string masterKey, bool isPublic)
        {
            BackgroundWorker bw = new BackgroundWorker();
            bw.WorkerReportsProgress = true;

            bw.RunWorkerCompleted += (sender, args) =>
            {
                if (DownloadCompleted != null)
                {
                    DownloadCompleted(args.Result as string);
                }
            };

            bw.ProgressChanged += (sender, args) =>
            {
                if (ProgressChanged != null)
                {
                    ProgressChanged(args.UserState as string);
                }
            };

            bw.DoWork += (sender, args) =>
            {
                try
                {
                    bw.ReportProgress(0, "Creating Download Job.");

                    string dataflowJobLocation = CreateDownloadJob(accessId, dataSourceName, masterKey);

                    //Continue to check the dataflow job status until the job has completed
                    Response statusDetails;
                    DataflowJob job = null;
                    do
                    {
                        statusDetails = CheckStatus(dataflowJobLocation, masterKey);

                        if (statusDetails != null
                            && statusDetails.ResourceSets != null
                            && statusDetails.ResourceSets.Length > 0
                            && statusDetails.ResourceSets[0] != null
                            && statusDetails.ResourceSets[0].Resources != null
                            && statusDetails.ResourceSets[0].Resources.Length > 0
                            && statusDetails.ResourceSets[0].Resources[0] != null)
                        {
                            job = statusDetails.ResourceSets[0].Resources[0];
                            if (job.Status == "Aborted")
                            {
                                bw.ReportProgress(0, "Download Job Aborted.");
                                throw new Exception("Job was aborted due to an error.");
                            }
                        }

                        if (job == null || job.Equals("Pending"))
                        {
                            Thread.Sleep(15000); //Get status every 15 seconds
                        }
                    }
                    while (job != null && job.Equals("Pending"));

                    StringBuilder log = new StringBuilder();

                    if (job.Status.Equals("Completed"))
                    {
                        bw.ReportProgress(0, "Download Complete, creating Excel file.");

                        string downloadURL = null;
                        foreach (Link l in job.Links)
                        {
                            if (l.Role.Equals("output") && l.Name != null && l.Name.Equals("succeeded"))
                            {
                                log.AppendFormat("Download URL: {0}\r\n", l.URL);
                                downloadURL = l.URL;
                                break;
                            }
                        }

                        if (!string.IsNullOrWhiteSpace(downloadURL))
                        {
                            WebClient wc = new WebClient();
                            using(System.IO.Stream xmlStream = wc.OpenRead(downloadURL + "?key=" + masterKey))
                            {
                                bw.ReportProgress(0, "Downloading Data source.");

                                System.Data.DataSet dataSet = new System.Data.DataSet();
                                dataSet.ReadXml(xmlStream);
                                    
                                Excel.Application app = new Excel.Application();
                                Excel.Workbook book = app.Workbooks.Add();

                                System.Data.DataTable dt = dataSet.Tables[0];

                                Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets.Add();

                                //Add settings to workbook
                                Settings s = new Settings(book)
                                {
                                    DataSourceName = dataSourceName,
                                    EntityTypeName = dt.TableName,
                                    MasterKey = masterKey,
                                    IsPublic = isPublic
                                };
                                s.Save(book);

                                //Add Schema Information
                                sheet.Cells[1, 1] = string.Format(Constants.BingDataSchemaType, dt.TableName);

                                //Get Primary Key
                                System.Data.DataColumn pKey = dt.PrimaryKey[0];

                                for (int j = 0; j < dt.Columns.Count; j++)
                                {
                                    //Set the data type of all columns in excel to text so that numbers do not get incorrectly formatted.
                                    (sheet.Cells[2, j + 1] as Excel.Range).EntireColumn.NumberFormat = "@";
                                }

                                //Write data to file
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {
                                    for (int j = 0; j < dt.Columns.Count; j++)
                                    {
                                        if (i == 0)
                                        {
                                            //Write Column Names
                                            if (dt.Columns[j] == pKey)
                                            {
                                                sheet.Cells[2, j + 1] = string.Format("{0}({1},primaryKey)", dt.Columns[j].ColumnName, DataTypes.GetTypeName(dt.Columns[j].DataType));
                                            }
                                            else
                                            {
                                                sheet.Cells[2, j + 1] = string.Format("{0}({1})", dt.Columns[j].ColumnName, DataTypes.GetTypeName(dt.Columns[j].DataType));
                                            }
                                        }

                                        sheet.Cells[i + 3, j + 1] = dt.Rows[i][j].ToString();
                                    }
                                }

                                book.SaveAs(fileName, Excel.XlSaveAsAccessMode.xlNoChange);
                                app.Visible = true;
                                //app.Quit();
                            }
                        }
                    }
                    else if (job.Status.Equals("Aborted"))
                    {
                        log.AppendLine("Download Aborted.\r\n");
                    }

                    log.AppendFormat("Total Entities{0}\r\n", job.TotalEntityCount);

                    args.Result = log.ToString();
                }
                catch (Exception e)
                {
                    args.Result = "Error: " + e.Message;
                }
            };
            bw.RunWorkerAsync();
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Creates a geocode dataflow job and uploads spatial data to process.
        /// </summary>
        /// <param name="dataFeed">The address data to geocode.</param>
        /// <param name="bingMapsKey">The Bing Maps Key to use for this job. The same key is used to get job status and download results.</param>
        /// <param name="description">Text that is used to describe the geocode dataflow job. </param>
        /// <returns>A URL that defines the location of the geocode dataflow job that was created.</returns>
        private static string CreateUploadJob(Settings settings)
        {
            //TODO: Consider using zipped XML file.

            //Build the HTTP URI that will upload and create the geocode dataflow job
            string url = string.Format("https://spatial.virtualearth.net/REST/v1/Dataflows/LoadDataSource?dataSourceName={0}&loadOperation=complete&setPublic={1}&input=pipe&output=json&key={2}",
                settings.DataSourceName,
                settings.IsPublic ? "1" : "0",
                Uri.EscapeUriString(settings.MasterKey));

            if (!string.IsNullOrWhiteSpace(settings.QueryKey))
            {
                url += "&queryKey=" + Uri.EscapeUriString(settings.QueryKey);
            }

            Uri createJobUri = new Uri(url);

            //Include the data to geocode in the HTTP request
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(createJobUri);

            // The HTTP method must be 'POST'.
            request.Method = "POST";
            request.ContentType = "text/plain";

            using (Stream requestStream = request.GetRequestStream())
            {
                var sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
                DataSourceTools.ExportDelimitedDataSource(sheet, requestStream, '|', settings);
            }

            //Submit the HTTP request and check if the job was created successfully. 
            using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
            {
                // If the job was created successfully, the status code should be
                // 201 (Created) and the 'Location' header should contain a URL
                // that defines the location of the new dataflow job. You use this 
                // URL with the Bing Maps Key to query the status of your job.
                if (response.StatusCode != HttpStatusCode.Created)
                {
                    throw new Exception("An HTTP error status code was encountered when creating the geocode job.");
                }

                string dataflowJobLocation = response.GetResponseHeader("Location");
                if (String.IsNullOrEmpty(dataflowJobLocation))
                {
                    throw new Exception("The 'Location' header is missing from the HTTP response when creating a goecode job.");
                }

                return dataflowJobLocation;
            }
        }

        private static string CreateDownloadJob(string accessID, string dataSourceName, string masterKey)
        {
            //Build the HTTP URI that will upload and create the geocode dataflow job
            string url = string.Format("https://spatial.virtualearth.net/REST/v1/Dataflows/DataSourceDownload/{0}/{1}?output=json&key={2}",
                accessID,
                dataSourceName,
                Uri.EscapeUriString(masterKey));

            Uri createJobUri = new Uri(url);

            //Include the data to geocode in the HTTP request
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(createJobUri);
            request.Method = "GET";

            //Submit the HTTP request and check if the job was created successfully. 
            using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
            {
                // If the job was created successfully, the status code should be
                // 201 (Created) and the 'Location' header should contain a URL
                // that defines the location of the new dataflow job. You use this 
                // URL with the Bing Maps Key to query the status of your job.
                if (response.StatusCode != HttpStatusCode.Created)
                {
                    throw new Exception("An HTTP error status code was encountered when creating the geocode job.");
                }

                string dataflowJobLocation = response.GetResponseHeader("Location");
                if (String.IsNullOrEmpty(dataflowJobLocation))
                {
                    throw new Exception("The 'Location' header is missing from the HTTP response when creating a goecode job.");
                }

                return dataflowJobLocation;
            }
        }

        /// <summary>
        /// Checks the status of a dataflow job and defines the URLs to use to download results when the job is completed.
        /// </summary>
        /// <param name="dataflowJobLocation">The URL to use to check status for a job.</param>
        /// <param name="bingMapsKey">The Bing Maps Key for this job. The same key is used to create the job and download results.</param>
        /// <returns>
        /// A DownloadDetails object that contains the status of the geocode dataflow job (Completed, Pending, Aborted). 
        /// When the status is set to Completed, DownloadDetails also contains the links to download the results
        /// </returns>
        private static Response CheckStatus(string dataflowJobLocation, string bingMapsKey)
        {
            //Build the HTTP Request to get job status
            UriBuilder uriBuilder = new UriBuilder(dataflowJobLocation + @"?key=" + bingMapsKey + "&output=json");
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uriBuilder.Uri);

            request.Method = "GET";

            //Submit the request and read the response to get job status and to retrieve the links for downloading the job results
            using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
            {
                if (response.StatusCode != HttpStatusCode.OK)
                {
                    throw new Exception("An HTTP error status code was encountered when checking job status.");
                }

                using (Stream receiveStream = response.GetResponseStream())
                {
                    DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(Response));
                    return ser.ReadObject(receiveStream) as Response;
                }
            }
        }

        #endregion
    }
}
